package cn.com.nes.site.module.dao;

import cn.com.nes.common.util.*;
import cn.com.nes.site.entity.DataReq;
import cn.com.nes.site.entity.PageData;
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson2.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.TransactionStatus;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

@Repository
public class CompanyDaoImpl extends BaseDaoImpl {

    private TransactionStatus newg_status = null;

    public PageData saveCompanyToExpand(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        param.put("tableName", "t_company_to_expand");
        param.put("createCompany", req.getUinfo().getCompanyId());
        newg_status = begin_newg();// 开启事务
        try {
            PageData sqlInfo = getInsertSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());

            commit_newg(newg_status);
            res.setKey((String) param.get("uuid"));
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData getCompanyToExpand(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取失败");
        try {

            String companyName = (String) param.get("companyName");
            String address = (String) param.get("address");
            String legalPerson = (String) param.get("legalPerson");
            String areacode = (String) param.get("areacode");

            StringBuffer sb = new StringBuffer();
            sb.append("select a.*, b.xzqhmc areaName from t_company_to_expand a");
            sb.append(" left join t_xzqh b on a.areacode = b.xzqhdm");
            sb.append(" where a.createCompany = '").append(req.getUinfo().getCompanyId()).append("'");
            sb.append(" and a.signed = 0 and a.companyName <> ''");
            if (StrUtil.isNotEmpty(companyName)) {
                sb.append(" and a.companyName like '%").append(companyName).append("%'");
            }
            if (StrUtil.isNotEmpty(address)) {
                sb.append(" and a.address like '%").append(address).append("%'");
            }
            if (StrUtil.isNotEmpty(legalPerson)) {
                sb.append(" and a.legalPerson like '%").append(legalPerson).append("%'");
            }
            if (StrUtil.isNotEmpty(areacode) && !"86".equals(areacode)) {
                sb.append(" and a.areacode like '")
                        .append(XzqhUtil.getQueryXzqh(this.getJdbcTemplate_newg(), areacode))
                        .append("%' ");
            }

            sb.append("  order by a.createTime desc ");

            sb = DBUtils.sql_check(sb);

            List<Map<String, Object>> list = pageQuery(req, res, sb);
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    private List<Map<String, Object>> pageQuery(DataReq req, PageData res, StringBuffer sql) throws SQLException {
        StringBuffer sbfrom = new StringBuffer();
        sbfrom.append(sql.toString());
        int idx = sbfrom.indexOf(" from ");
        sbfrom.delete(0, idx);
        Page page = new Page(this.getJdbcTemplate_newg(), sbfrom.toString(), req.getPageid(),
                req.getPagesize(), res, new Object[]{});
        sql.append(" limit ").append(page.getOffsetlimit()).append(",").append(req.getPagesize());
        List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sql.toString(), new Object[]{});
        return list;
    }

    public PageData getCompanyFlagMap(DataReq req) {
        req.setPagesize(9999);
        req.setPageid(1);
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取失败");
        try {

            String companyName = (String) param.get("companyName");
            String address = (String) param.get("address");
            String legalPerson = (String) param.get("legalPerson");
            String areacode = (String) param.get("areacode");

            StringBuffer sb = new StringBuffer();
            sb.append("select * from t_company_to_expand");
            sb.append(" where createCompany = '").append(req.getUinfo().getCompanyId()).append("'");

            if (StrUtil.isNotEmpty(companyName)) {
                sb.append(" and companyName like '%").append(companyName).append("%'");
            }
            if (StrUtil.isNotEmpty(address)) {
                sb.append(" and address like '%").append(address).append("%'");
            }
            if (StrUtil.isNotEmpty(legalPerson)) {
                sb.append(" and legalPerson like '%").append(legalPerson).append("%'");
            }
            if (StrUtil.isNotEmpty(areacode) && !"86".equals(areacode)) {
                sb.append(" and areacode like '")
                        .append(XzqhUtil.getQueryXzqh(this.getJdbcTemplate_newg(), areacode))
                        .append("%' ");
            }

            sb.append(" order by createTime desc ");

            sb = DBUtils.sql_check(sb);

            List<Map<String, Object>> list = pageQuery(req, res, sb);
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    //备选代理商转正
    public PageData convertToFormalCompany(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");

        String uuid = (String) param.get("uuid");
        String level = (String) param.get("level");
        param.remove("level");
        final String userCompanyId = req.getUinfo().getCompanyId();
        final String operateUserId = req.getUinfo().getId();
        newg_status = begin_newg();// 开启事务
        try {
            //查询备选代理商信息
            StringBuffer querySql = new StringBuffer();
            querySql.append("select uuid, companyName, address, legalPerson, contactNumber, areacode,")
                    .append(" cast(longitude as char), cast(latitude as char) from t_company_to_expand ")
                    .append("where uuid = '").append(uuid).append("'");
            final Map<String, Object> company = this.getJdbcTemplate_newg().queryForMap(querySql.toString());
            param.remove("uuid");

            //是否已有正式代理商
            querySql.delete(0, querySql.length());
            querySql.append("select companyId from t_company where companyName = '").append(company.get("companyName"))
                    .append("'");

            String companyId = this.getJdbcTemplate_newg().query(querySql.toString(),
                    new ResultSetExtractor<String>() {
                        @Override
                        public String extractData(ResultSet rs) throws SQLException, DataAccessException {
                            return rs.next() ? rs.getString("companyId") : null;
                        }
                    });

            if (StrUtil.isNotEmpty(companyId)) {
                StringBuffer sb = new StringBuffer();
                //添加厂商供应商对应关系
                sb.append(" INSERT INTO t_agent_facturer (agent_id,facturer_id, gmt_create_id) VALUES (?,?,?)");
                this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{companyId, userCompanyId, operateUserId});

                //更新备选代理商状态
                sb.delete(0, sb.length());
                sb.append("update t_company_to_expand set signed = 1, level = ? where uuid = ?");
                this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{level, uuid});

                commit_newg(newg_status);
            } else {
                //生成公司id
                companyId = getNextId("0000");
                param.put("tableName", "t_company");
                company.put("p_companyId", "0000");
                company.put("p_companyName", "全部");
                company.put("companyType", "02");
                company.put("companyId", companyId);
                company.remove("uuid");
                param.putAll(company);

                PageData sqlInfo = getInsertSQL(req);
                if (sqlInfo.getRetCode() != 0) {
                    rollback_newg(newg_status);
                    return sqlInfo;
                }
                StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
                List<String> listData = (List<String>) sqlInfo.getData().get("listData");
                this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());

                //删除厂商供应商对应关系
                StringBuffer sb = new StringBuffer();
                sb.delete(0, sb.length());
                sb.append(" update t_agent_facturer set deleted = '1' ");
                sb.append(" WHERE agent_id = ? ");
                this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{companyId});
                //添加厂商供应商对应关系
                sb.delete(0, sb.length());
                sb.append(" INSERT INTO t_agent_facturer (agent_id,facturer_id) VALUES (?,?)");
                this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{companyId, userCompanyId});

                //更新备选代理商状态
                sb.delete(0, sb.length());
                sb.append("update t_company_to_expand set signed = 1, level = ? where uuid = ?");
                this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{level, uuid});


                commit_newg(newg_status);
            }

            res.setKey(companyId);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData submitCompanyInformation(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        param.put("tableName", "t_agent_intent_application");
        //param.put("key", "id");
        param.put("agentId", req.getUinfo().getCompanyId());
        if (param.containsKey("areaCode")) {
            param.put("areacode", param.get("areaCode"));
            param.remove("areaCode");
        }
        newg_status = begin_newg();// 开启事务
        try {
            PageData sqlInfo = getInsertSQL(req);
            if (sqlInfo.getRetCode() != 0) {
                rollback_newg(newg_status);
                return sqlInfo;
            }
            final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
            final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
            this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());

            commit_newg(newg_status);
//            res.setKey((String) param.get("uuid"));
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData getAgentIntentApplications(DataReq req) {
        Map<String, Object> param = req.getReqData();
        String type = (String) param.get("type");
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("获取失败");
        String state = (String) param.get("state");
        try {
            if (StrUtil.isBlank(type)) {
                res.setPage(Collections.<Map<String, Object>>emptyList());
                res.setRetCode(0);
                res.setRetMessage("OK");
                return res;
            }

            StringBuffer sb = new StringBuffer();
            sb.append("select a.*, b.companyName as targetCompanyName,d.idcard_image_front,d.idcard_images_back,f.comment,f.pass, ")
                    .append(" c.xzqhmc as areaName,DATE_FORMAT(a.createTime,'%Y-%m-%d %H:%i:%s') createTime ")
                    .append(" ,taf.id as agentRelationshipId ")
                    .append(" from t_agent_intent_application a")
                    .append(" inner join t_company b on a.targetCompanyId = b.companyId")
                    .append(" left join t_company e on a.agentId=e.companyId")
                    .append(" left join (SELECT m.business_id, m.idcard_image_front, m.idcard_images_back,m.createTime FROM baidu_realface m WHERE m. STATUS = '1' GROUP BY m.business_id HAVING m.createTime=max(m.createTime)) d on e.cretUserId=d.business_id  ")
                    .append(" left join t_agent_facturer taf on taf.application_id = a.id ")
                    .append(" left join t_xzqh c on c.xzqhdm = a.areacode");
            //代理商查所有记录  厂商查一条记录
            if ("1".equals(type)) {
            	sb.append(" left join (select ta.id,ta.intentId,ta.pass,ta.`comment`,ta.auditorId,ta.auditTime,max(ta.auditTime) maxTime from t_agent_intent_audit_record ta group by ta.intentId) f on a.id=f.intentId ");
            }else {
            	sb.append(" left join t_agent_intent_audit_record f on a.id=f.intentId ");
            }
                    
            if ("00".equals(req.getUinfo().getCompanyId())) {
                sb.append(" where 1=1");
            } else if ("1".equals(type)) {
                sb.append(" where a.targetCompanyId = '").append(req.getUinfo().getCompanyId()).append("'");
            } else {
                sb.append(" where a.agentId = '").append(req.getUinfo().getCompanyId()).append("'");
            }
            if (StrUtil.isNotBlank(state)) {
                sb.append(" and a.state = ").append(state);
            }
            sb.append(" order by a.createTime desc,f.auditTime desc");

            DBUtils.sql_check(sb);

            List<Map<String, Object>> list = pageQuery(req, res, sb);
            for (Map<String, Object> map : list) {
                if (!StringUtils.isEmpty((String)map.get("idCard")) && map.containsKey("idCard")) {
                    String idCard = OssUtil.getImgSignURL((String) map.get("idCard"), 0);
                    map.put("idCardReal", idCard);
                }
                if (!StringUtils.isEmpty((String)map.get("idCardBack")) && map.containsKey("idCardBack")) {
                    String idCardBack = OssUtil.getImgSignURL((String) map.get("idCardBack"), 0);
                    map.put("idCardBackReal", idCardBack);
                }
                if (!StringUtils.isEmpty((String)map.get("businessLicense")) && map.containsKey("businessLicense")) {
                    String businessLicense = OssUtil.getImgSignURL((String) map.get("businessLicense"), 0);
                    map.put("businessLicenseReal", businessLicense);
                }
                if (map.containsKey("state")) {
                    int stateResult = (int) map.get("state");
                    map.put("stateName",
                            stateResult == 0
                                    ? "待审核" : stateResult == 1
                                    ? "审核通过" : stateResult == 2
                                    ? "审核不通过" : "未知");
                }
            }
            res.setPage(list);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData auditAgentIntentApplication(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        String companyId = req.getUinfo().getCompanyId();
        String agentId = (String) param.get("agentId");
        String pass = (String) param.get("pass");
        //申请id
        String id = (String) param.get("id");
        String comment = (String) param.get("comment");
        String userId = req.getUinfo().getId();

        if (StrUtil.hasBlank(companyId, agentId, pass, id)) {
            return res;
        }
        newg_status = begin_newg();// 开启事务
        try {
            StringBuffer sb = new StringBuffer();
            //添加厂商供应商对应关系
            if ("1".equals(pass)) {
                sb.append("INSERT INTO t_agent_facturer (agent_id,facturer_id, gmt_create_id, application_id) VALUES (?,?,?,?)");
                this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{agentId, companyId, userId, id});
                sb.delete(0, sb.length());
            }

            sb.append("update t_agent_intent_application set state = ?")
                    .append(" where id = ?");

            this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{pass.equals("1") ? 1 : 2, id});

            //记录
            sb.delete(0, sb.length());
            sb.append("insert into t_agent_intent_audit_record (intentId, pass, comment, auditorId) ")
                    .append("values (?, ?, ?, ?)");
            this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{id, pass, comment, userId});

            commit_newg(newg_status);

            res.setKey(companyId);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData editCompanyInformation(DataReq req) {
        Map<String, Object> param = req.getReqData();
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        Integer id = (Integer) param.get("id");
        String companyName = (String) param.get("companyName");
        String address = (String) param.get("address");
        String areacode = (String) param.get("areacode");
        String legalPerson = (String) param.get("legalPerson");
        String legalPersonIDCardNumber = (String) param.get("legalPersonIDCardNumber");
        String idCard = (String) param.get("idCard");
        String idCardBack = (String) param.get("idCardBack");
        String uniformSocialCreditCode = (String) param.get("uniformSocialCreditCode");
        String contactPerson = (String) param.get("contactPerson");
        String position = (String) param.get("position");
        String contactPhoneNumber = (String) param.get("contactPhoneNumber");
        String contactEmail = (String) param.get("contactEmail");
        String businessLicense = (String) param.get("businessLicense");
        String registrationNumber = (String) param.get("registrationNumber");
        String userId = req.getUinfo().getId();
        if (StrUtil.isAllBlank(companyName, areacode, legalPerson, legalPersonIDCardNumber, idCard, idCardBack,
                uniformSocialCreditCode, contactPerson, position, contactPhoneNumber, contactEmail, businessLicense,
                registrationNumber)) {
            res.setRetCode(0);
            res.setRetMessage("OK");
            return res;
        }
        newg_status = begin_newg();// 开启事务
        try {
            List<Object> updateParams = new ArrayList<Object>();
            StringBuffer sb = new StringBuffer();
            sb.append("update t_agent_intent_application set ");
            if (StrUtil.isNotBlank(companyName)) {
                sb.append("companyName = ?,");
                updateParams.add(companyName);
            }
            if (StrUtil.isNotBlank(address)) {
                sb.append("address = ?,");
                updateParams.add(address);
            }
            if (StrUtil.isNotBlank(areacode)) {
                sb.append("areacode = ?,");
                updateParams.add(areacode);
            }
            if (StrUtil.isNotBlank(legalPerson)) {
                sb.append("legalPerson = ?,");
                updateParams.add(legalPerson);
            }
            if (StrUtil.isNotBlank(legalPersonIDCardNumber)) {
                sb.append("legalPersonIDCardNumber = ?,");
                updateParams.add(legalPersonIDCardNumber);
            }
            if (StrUtil.isNotBlank(idCard)) {
                sb.append("idCard = ?,");
                updateParams.add(idCard);
            }
            if (StrUtil.isNotBlank(idCardBack)) {
                sb.append("idCardBack = ?,");
                updateParams.add(idCardBack);
            }
            if (StrUtil.isNotBlank(uniformSocialCreditCode)) {
                sb.append("uniformSocialCreditCode = ?,");
                updateParams.add(uniformSocialCreditCode);
            }
            if (StrUtil.isNotBlank(contactPerson)) {
                sb.append("contactPerson = ?,");
                updateParams.add(contactPerson);
            }
            if (StrUtil.isNotBlank(position)) {
                sb.append("position = ?,");
                updateParams.add(position);
            }
            if (StrUtil.isNotBlank(contactPhoneNumber)) {
                sb.append("contactPhoneNumber = ?,");
                updateParams.add(contactPhoneNumber);
            }
            if (StrUtil.isNotBlank(contactEmail)) {
                sb.append("contactEmail = ?,");
                updateParams.add(contactEmail);
            }
            if (StrUtil.isNotBlank(businessLicense)) {
                sb.append("businessLicense = ?,");
                updateParams.add(businessLicense);
            }
            if (StrUtil.isNotBlank(registrationNumber)) {
                sb.append("registrationNumber = ?,");
                updateParams.add(registrationNumber);
            }
            sb.append("modifyTime = ? ,modifyUid = ? , state = 0 where id = ? and state <> 1");
            updateParams.add(new Date());
            updateParams.add(userId);
            updateParams.add(id);
            this.getJdbcTemplate_newg().update(sb.toString(), updateParams.toArray());
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData getAllTargetCompany(DataReq req) {
        PageData res = new PageData();
        List<Map<String, Object>> list;
        StringBuffer sb = new StringBuffer();
        String agentId = req.getUinfo().getCompanyId();
        try {
            sb.append("select companyId, companyName from t_company where sfsc <> 1 and companyType = '01' ")
                    .append(" and not exists(select 1 from t_agent_facturer ")
                    .append("where companyId = t_company.id and agent_id = ?)")
                    .append(" order by companyName");
            list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{agentId});
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData saveCompanyPromotionCard(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        Map<String, Object> param = req.getReqData();
        String uuid = (String) param.get("uuid");
        param.put("tableName", "promotion_card_pattern");
        String companyId = req.getUinfo().getCompanyId();
        param.put("companyId", companyId);
        param.put("auditState", "0");
        newg_status = begin_newg();
        try {
            //根据公司id查询
            StringBuffer sb = new StringBuffer();
            sb.append("select auditState from promotion_card_pattern where companyId = ? and sfsc = 0");
            if (StrUtil.isNotBlank(uuid)) sb.append(" and uuid = '").append(uuid).append("' and auditState = 1");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId});

            if (StrUtil.isBlank(uuid)) { //新增
                //没有卡片时可以新增，或有一个已过审核的卡片可以新增
                if (CollUtil.isNotEmpty(list)) {
                    if (list.size() > 1) {
                    	rollback_newg(newg_status);
                    	return res;
                    }
                    int auditState = (int) list.get(0).get("auditState");
                    if (auditState != 1) {
                    	rollback_newg(newg_status);
                    	return res;
                    }
                }
                Set<String> set = new HashSet<>();
                set.add("tableName");
                set.add("companyId");
                set.add("companyName");
                set.add("companyLogo");
                set.add("promotionValue1");
                set.add("promotionValue1Unit");
                set.add("promotionValue1Title");
                set.add("promotionValue2");
                set.add("promotionValue2Unit");
                set.add("promotionValue2Title");
                set.add("title");
                set.add("content");
                set.add("poster");
                Iterator<Map.Entry<String, Object>> iterator = param.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry<String, Object> entry = iterator.next();
                    if (!set.contains(entry.getKey())) iterator.remove();
                }
                PageData sqlInfo = getInsertSQL(req);
                if (sqlInfo.getRetCode() != 0) {
                    rollback_newg(newg_status);
                    return sqlInfo;
                }
                final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
                final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
                this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());
            } else { //修改
                //不能修改已过审核的卡片
                if (CollUtil.isNotEmpty(list)) {
                    res.setRetMessage("不能修改已过审发布的卡片");
                    rollback_newg(newg_status);
                    return res;
                }
                String companyName = (String) req.getReqData().get("companyName");
                String companyLogo = (String) req.getReqData().get("companyLogo");
                String promotionValue1 = (String) req.getReqData().get("promotionValue1");
                String promotionValue1Unit = (String) req.getReqData().get("promotionValue1Unit");
                String promotionValue1Title = (String) req.getReqData().get("promotionValue1Title");
                String promotionValue2 = (String) req.getReqData().get("promotionValue2");
                String promotionValue2Unit = (String) req.getReqData().get("promotionValue2Unit");
                String promotionValue2Title = (String) req.getReqData().get("promotionValue2Title");
                String title = (String) req.getReqData().get("title");
                String content = (String) req.getReqData().get("content");
                String poster = (String) req.getReqData().get("poster");

                sb.delete(0, sb.length());
                List<String> updateParams = new ArrayList<String>();
                sb.append("update promotion_card_pattern set companyName = ?, companyLogo = ?, promotionValue1 = ?,")
                        .append(" promotionValue1Unit = ?, promotionValue1Title = ?, promotionValue2 = ?,")
                        .append(" promotionValue2Unit = ?, promotionValue2Title = ?, title = ?, content = ?,")
                        .append(" poster = ?, modifyTime = now(), modifyUid = ?")
                        .append(" where uuid = ?");
                updateParams.add(companyName);
                updateParams.add(companyLogo);
                updateParams.add(promotionValue1);
                updateParams.add(promotionValue1Unit);
                updateParams.add(promotionValue1Title);
                updateParams.add(promotionValue2);
                updateParams.add(promotionValue2Unit);
                updateParams.add(promotionValue2Title);
                updateParams.add(title);
                updateParams.add(content);
                updateParams.add(poster);
                updateParams.add(req.getUinfo().getId());
                updateParams.add(uuid);
                this.getJdbcTemplate_newg().update(sb.toString(), updateParams.toArray());
            }
            commit_newg(newg_status);
            res.setKey((String) param.get("uuid"));
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData saveCompanyPromotionBanner(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        Map<String, Object> param = req.getReqData();
        String uuid = (String) param.get("uuid");
        param.put("tableName", "promotion_banner_pattern");
        param.put("companyId", req.getUinfo().getCompanyId());
        newg_status = begin_newg();
        try {
            //根据公司id查询
            StringBuffer sb = new StringBuffer();
            sb.append("select auditState from promotion_card_pattern where companyId = ? and sfsc = 0");
            if (StrUtil.isNotBlank(uuid)) sb.append(" and uuid = ? and auditState = 1");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{uuid});
            if (StrUtil.isBlank(uuid)) { //新增
                if (CollUtil.isNotEmpty(list)) {
                    if (list.size() > 1)  {
                        rollback_newg(newg_status);
                        return res;
                    }
                    int auditState = (int) list.get(0).get("auditState");
                    if (auditState != 1) {
                        rollback_newg(newg_status);
                        return res;
                    }
                }
                param.remove("uuid");
                PageData sqlInfo = getInsertSQL(req);
                if (sqlInfo.getRetCode() != 0) {
                    rollback_newg(newg_status);
                    return sqlInfo;
                }
                final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
                final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
                this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());
            } else { //修改
                //不能修改已过审核的卡片
                if (CollUtil.isNotEmpty(list)) {
                    res.setRetMessage("不能修改已过审发布的卡片");
                    rollback_newg(newg_status);
                    return res;
                }

                String banner = (String) req.getReqData().get("banner");
                sb.delete(0, sb.length());
                List<String> updateParams = new ArrayList<String>();
                sb.append("update promotion_banner_pattern set banner = ?, modifyTime = now(), modifyUid = ?")
                        .append(" ,set auditState = 0")
                        .append(" where uuid = ?");
                updateParams.add(banner);
                updateParams.add(req.getUinfo().getId());
                updateParams.add(uuid);
                this.getJdbcTemplate_newg().update(sb.toString(), updateParams.toArray());
            }
            commit_newg(newg_status);
            res.setKey((String) param.get("uuid"));
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
            rollback_newg(newg_status);
        }
        return res;

    }

    public PageData savePromotionDisplay(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        Map<String, Object> param = req.getReqData();
        String cardId = (String) param.get("cardId");
        param.put("tableName", "promotion_card_display");
        newg_status = begin_newg();
        try {
            StringBuffer sb = new StringBuffer();
            sb.append("select exists(select 1 from promotion_card_display where cardId = ? and sfsc = 0)");
            int count = this.getJdbcTemplate_newg().queryForObject(sb.toString(), new Object[]{cardId}, Integer.class);

            if (0 == count) { //新增
                PageData sqlInfo = getInsertSQL(req);
                if (sqlInfo.getRetCode() != 0) {
                    rollback_newg(newg_status);
                    return sqlInfo;
                }
                final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
                final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
                this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());
            } else { //修改
                String exposure = (String) param.get("exposure");
                sb.delete(0, sb.length());
                List<String> updateParams = new ArrayList<String>();
                sb.append("update promotion_card_display set exposure = ?, modifyTime = now(), modifyUid = ?")
                        .append(" where cardId = ?");
                updateParams.add(exposure);
                updateParams.add(req.getUinfo().getId());
                updateParams.add(cardId);
                this.getJdbcTemplate_newg().update(sb.toString(), updateParams.toArray());
            }
            commit_newg(newg_status);
            res.setKey((String) param.get("cardId"));
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
        	rollback_newg(newg_status);
            e.printStackTrace();
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData savePromotionBannerDisplay(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        Map<String, Object> param = req.getReqData();
        String bannerId = (String) param.get("bannerId");
        param.put("tableName", "promotion_banner_display");
        param.put("companyId", req.getUinfo().getCompanyId());
        newg_status = begin_newg();
        try {
            StringBuffer sb = new StringBuffer();
            sb.append("select exists(select 1 from promotion_banner_display where bannerId = ? and sfsc = 0)");
            int count = this.getJdbcTemplate_newg().queryForObject(sb.toString(), new Object[]{bannerId}, Integer.class);
            if (0 == count) { //新增
                String seq = (String) param.get("seq");

                //顺序为空时，自动生成
                if (StrUtil.isBlank(seq)) {
                    sb.delete(0, sb.length());
                    sb.append("select max(seq) from promotion_banner_display where sfsc = 0");
                    List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString());
                    if (list != null && list.size() > 0) {
                        Map<String, Object> map = list.get(0);
                        if (map.get("max(seq)") != null) {
                            final int nextSeq = (Integer) map.get("max(seq)") + 1;
                            param.put("seq", String.valueOf(nextSeq));
                        } else {
                            param.put("seq", "1");
                        }
                    } else {
                        param.put("seq", "1");
                    }
                }

                PageData sqlInfo = getInsertSQL(req);
                if (sqlInfo.getRetCode() != 0) {
                    rollback_newg(newg_status);
                    return sqlInfo;
                }
                final StringBuffer sb_field = (StringBuffer) sqlInfo.getData().get("sb_field");
                final List<String> listData = (List<String>) sqlInfo.getData().get("listData");
                this.getJdbcTemplate_newg().update(sb_field.toString(), listData.toArray());
            } else { //修改
                String banner = (String) param.get("banner");
                String seq = (String) param.get("seq");
                sb.delete(0, sb.length());
                sb.append("select max(seq) from promotion_banner_display");
                List<Map<String, Object>> query =
                        this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{seq});
                boolean increase = false;
                if (CollUtil.isNotEmpty(query)) {
                    if (query.get(0).get("max(seq)") != null) {
                        int maxSeq = (int) query.get(0).get("max(seq)");
                        increase = maxSeq >= Integer.parseInt(seq);
                    }
                }
                //增加序列
                if (increase) {
                    sb.delete(0, sb.length());
                    sb.append("update promotion_banner_display set seq = seq + 1 where seq > ?");
                    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{seq});
                    sb.append("update promotion_banner_display set seq = seq - 1 where seq = ?");
                    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{seq});
                }

                sb.delete(0, sb.length());
                List<String> updateParams = new ArrayList<String>();
                sb.append("update promotion_banner_display set banner = ?, seq = ?, modifyTime = now(), modifyUid = ?")
                        .append(" where bannerId = ?");
                updateParams.add(banner);
                updateParams.add(seq);
                updateParams.add(req.getUinfo().getId());
                updateParams.add(bannerId);
                this.getJdbcTemplate_newg().update(sb.toString(), updateParams.toArray());
            }
            commit_newg(newg_status);
            res.setKey((String) param.get("bannerId"));
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
        	rollback_newg(newg_status);
            e.printStackTrace();
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData auditCompanyPromotionCard(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        Map<String, Object> param = req.getReqData();
        final String userId = req.getUinfo().getId();
        String cardId = (String) param.get("cardId");
        String auditComment = (String) param.get("auditComment");
        String pass = (String) param.get("pass");
        String exposure = (String) param.get("exposure");
        String companyId = (String) param.get("companyId");
        newg_status = begin_newg();
        try {
            //update promotion_card
            String auditState = "1".equals(pass) ? "1" : "2";
            StringBuffer sb = new StringBuffer();
            sb.append("update promotion_card_pattern set auditState = ?, auditTime = now(), auditorId = ?,")
                    .append(" auditComment = ?").append(" where uuid = ?");
            this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{auditState, userId, auditComment, cardId});

            if ("1".equals(pass)) {
                //remove old card
                sb.delete(0, sb.length());
                sb.append("update promotion_card_pattern set sfsc = 1 where companyId = ? and uuid <> ?");
                this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{companyId, cardId});

                //insert promotion_card_display
                sb.delete(0, sb.length());
                sb.append("replace into promotion_card_display(cardId, companyId, exposure, createUid, createTime,")
                        .append(" modifyUid, modifyTime, sfsc)")
                        .append(" values(?, ?, ?, ?, now(), ?, now(), '0')");
                this.getJdbcTemplate_newg()
                        .update(sb.toString(), new Object[]{cardId, companyId, exposure, userId, userId});

                commit_newg(newg_status);
                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
        	rollback_newg(newg_status);
            e.printStackTrace();
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData auditCompanyPromotionBanner(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("保存失败");
        Map<String, Object> param = req.getReqData();
        final String userId = req.getUinfo().getId();
        String bannerId = (String) param.get("bannerId");
        String auditComment = (String) param.get("auditComment");
        String pass = (String) param.get("pass");
        String companyId = (String) param.get("companyId");
        String seq = (String) param.get("seq");
        newg_status = begin_newg();
        try {
            //update promotion_banner
            String auditState = "1".equals(pass) ? "1" : "2";
            StringBuffer sb = new StringBuffer();
            sb.append("update promotion_banner_pattern set auditState = ?, auditTime = now(), auditorId = ?,")
                    .append(" auditComment = ?").append(" where uuid = ?");
            this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{auditState, userId, auditComment, bannerId});

            if ("1".equals(pass)) {
                //insert promotion_banner_display
                sb.delete(0, sb.length());
                sb.append("select max(seq) from promotion_banner_display");
                List<Map<String, Object>> query =
                        this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
                boolean increase = false;
                if (CollUtil.isNotEmpty(query)) {
                    if (query.get(0).get("max(seAq)") != null) {
                        int maxSeq = (int) query.get(0).get("max(seq)");
                        increase = maxSeq >= Integer.parseInt(seq);
                    }
                }
                //增加序列
                if (increase) {
                    sb.delete(0, sb.length());
                    sb.append("update promotion_banner_display set seq = seq + 1 where seq > ?");
                    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{seq});
                    sb.append("update promotion_banner_display set seq = seq - 1 where seq = ?");
                    this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{seq});
                }

                //remove old banner
                sb.delete(0, sb.length());
                sb.append("update promotion_banner_pattern set sfsc = 1 where companyId = ? and uuid <> ?");
                this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{companyId, bannerId});

                //insert promotion_banner_display
                sb.delete(0, sb.length());
                sb.append("replace into promotion_banner_display(bannerId, companyId, seq, createUid, createTime,")
                        .append(" modifyUid, modifyTime, sfsc)");
                sb.append(" values(?, ?, ?, ?, now(), ?, now(), '0')");
                this.getJdbcTemplate_newg().update(sb.toString(), new Object[]{bannerId, companyId, seq, userId, userId});
                commit_newg(newg_status);
                res.setRetCode(0);
                res.setRetMessage("OK");
            }
        } catch (Exception e) {
        	rollback_newg(newg_status);
            e.printStackTrace();
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData getPromotionBanner(DataReq req) {
        PageData res = new PageData();
        List<Map<String, Object>> list;
        StringBuffer sb = new StringBuffer();
        //type, 0-全部，1-当前展示， 2-未审核，3-已过审未展示
        String type = (String) req.getReqData().get("type");
        type = StrUtil.isBlank(type) ? "0" : type;
        try {
            sb.append("select a.*, b.seq, c.companyName from promotion_banner_pattern a")
                    .append(" left join promotion_banner_display b on a.uuid = b.bannerId and b.sfsc = 0")
                    .append(" inner join t_company c on c.companyId = a.companyId")
                    .append(" where a.sfsc = 0");
            if ("1".equals(type)) {
                sb.append(" and b.id is not null and a.auditState = 1");
            }
            if ("2".equals(type)) {
                sb.append(" and a.auditState = 0");
            }
            if ("3".equals(type)) {
                sb.append(" and b.id is null and a.auditState = 1");
            }
            sb.append(" order by seq, a.createTime desc, a.companyId");
            list = this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{});
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData getPromotionCards(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询失败");
        StringBuffer sb = new StringBuffer();
        //0-全部 1-正在展示 2-未审核 3-已过审未展示
        String type = (String) req.getReqData().get("type");
        if (StrUtil.isBlank(type)) {
            type = "0";
        }
        try {
            sb.append("select a.*, b.exposure from promotion_card_pattern a");
            sb.append(" left join promotion_card_display b on a.uuid = b.cardId and b.sfsc = 0");
            sb.append(" where a.sfsc = 0 ");
            if ("1".equals(type)) {
                sb.append(" and a.auditState = 1 and b.id is not null");
            }
            if ("2".equals(type)) {
                sb.append(" and a.auditState = 0");
            }
            if ("3".equals(type)) {
                sb.append(" and a.auditState = 1 and b.id is null");
            }
            if ("1".equals(type)) {
                sb.append(" order by b.exposure desc, a.companyName");
            } else {
                sb.append(" order by a.createTime");
            }
            final List<Map<String, Object>> pageQuery = pageQuery(req, res, sb);
            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(pageQuery);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData getPromotionCardByCompanyId(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询失败");
        StringBuffer sb = new StringBuffer();
        String companyId = (String) req.getReqData().get("companyId");
        if (StrUtil.isBlank(companyId)) companyId = req.getUinfo().getCompanyId();
        //0-全部，1-已过审 2-待审核或者审核不通过
        String type = (String) req.getReqData().get("type");
        if (StrUtil.isBlank(type)) type = "0";
        try {
            sb.append("select a.* from promotion_card_pattern a")
                    .append(" where a.sfsc = 0 and a.companyId = ?");
            if ("1".equals(type)) sb.append(" and a.auditState = 1");
            if ("2".equals(type)) sb.append(" and a.auditState in (0,2)");
            sb.append(" order by a.auditState");
            List<Map<String, Object>> list =
                    this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId});

            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData getPromotionDisplayState(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询失败");
        StringBuffer sb = new StringBuffer();
        //1-卡片 2-banner
        String type = (String) req.getReqData().get("type");
        type = StrUtil.isBlank(type) ? "1" : type;
        String tableName = "1".equals(type) ? "promotion_card_pattern" : "promotion_banner_pattern";
        try {
            sb.append("select a.auditState, if(b.id is null, '未展示', '正在展示') as displayState")
                    .append(" from ").append(tableName).append(" a")
                    .append(" left join promotion_card_display b on a.uuid = b.cardId and b.sfsc = 0")
                    .append(" where a.companyId = ? and a.sfsc = 0")
                    .append(" order by auditState = 1");
            List<Map<String, Object>> list =
                    this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{req.getUinfo().getCompanyId()});
            if (CollUtil.isNotEmpty(list)) {
                int auditState = (int) list.get(0).get("auditState");
                StringBuffer auditStateDesc = new StringBuffer();

                switch (auditState) {
                    case 0:
                        auditStateDesc.append("未审核");
                        break;
                    case 1:
                        auditStateDesc.append("审核通过");
                        break;
                    case 2:
                        auditStateDesc.append("审核不通过");
                        break;
                    default:
                }

                if (list.size() > 1) {
                    auditStateDesc.append("，审核通过后将替换当前");
                    if ("1".equals(type)) sb.append("卡片");
                    if ("2".equals(type)) sb.append("轮播图");
                }

                list.get(0).put("auditStateDesc", auditStateDesc.toString());
                list.get(0).put("count", list.size());
            } else {
                list.add(new HashMap<String, Object>(1) {{
                    put("auditState", 0);
                    put("auditStateDesc", "未审核");
                    put("displayState", "未展示");
                    put("count", 0);
                }});
            }

            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list.subList(0, 1));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData removePromotionDisplay(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除失败");
        Map<String, Object> param = req.getReqData();
        //1-删除卡片，2-删除banner
        String uuid = (String) param.get("uuid");
        String type = (String) param.get("type");
        String table = "1".equals(type) ? "promotion_card_display" : "promotion_banner_display";
        String idColumn = "1".equals(type) ? "cardId" : "bannerId";
        newg_status = begin_newg();
        try {
            StringBuffer sb = new StringBuffer();
            List<String> updateParams = new ArrayList<String>();
            sb.append("delete from ").append(table).append(" where ").append(idColumn).append(" = ?");
//            sb.append("update ").append(table).append(" set sfsc = 1, modifyTime = now(), modifyUid = ?")
//                    .append("where ").append(idColumn).append(" = ?");
            updateParams.add(uuid);
            this.getJdbcTemplate_newg().update(sb.toString(), updateParams.toArray());
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }

    public PageData getPromotionBannerByCompanyId(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询失败");
        StringBuffer sb = new StringBuffer();
        String companyId = (String) req.getReqData().get("companyId");
        if (StrUtil.isBlank(companyId)) companyId = req.getUinfo().getCompanyId();
        //0-全部，1-已过审，2-待审核或者审核不通过
        String type = (String) req.getReqData().get("type");
        if (StrUtil.isBlank(type)) type = "0";
        try {
            sb.append("select a.* from promotion_banner_pattern a")
                    .append(" where a.sfsc = 0 and a.companyId = ?");
            if ("1".equals(type)) sb.append(" and a.auditState = 1");
            if ("2".equals(type)) sb.append(" and a.auditState in (0,2)");
            sb.append(" order by a.auditState = 1 desc");
            List<Map<String, Object>> list =
                    this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId});

            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData getCompanyInfo(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询失败");
        String companyId = (String) req.getReqData().get("companyId");
        if (StrUtil.isBlank(companyId)) companyId = req.getUinfo().getCompanyId();
        try {
            StringBuffer sb = new StringBuffer();
            sb.append("select a.companyName,if(areacode like '%;%', '', areacode) as areacode,a.address,a.legalPerson,")
                    .append(" a.uniformSocialCreditCode,a.businessLicense,companyId, b.xzqhmc as areaname, a.cretUserId")
                    .append(" from t_company a")
                    .append(" left join t_xzqh b on substring_index(a.areacode, ';', 1) = b.xzqhdm")
                    .append(" where a.companyId = ?");
            List<Map<String, Object>> list =
                    this.getJdbcTemplate_newg().queryForList(sb.toString(), new Object[]{companyId});
            if (list.size() > 0) {
                //查询认证信息
                Map<String, Object> result = list.get(0);
                Long cretUserId = (Long) result.get("cretUserId");

                List<Map<String, Object>> list2 = new ArrayList<>();
                if (cretUserId != null) {
                    sb.delete(0, sb.length());
                    sb.append("select idcard_name legalPerson, idcard_number legalPersonIDCardNumber")
                            .append(" from baidu_realface where business_id = ? and status = 1")
                            .append(" order by modifyTime desc limit 1");
                    list2 = this.getJdbcTemplate_newg().queryForList(sb.toString(),
                            String.valueOf(cretUserId));
                }
                if (list2.size() > 0) {
                    result.putAll(list2.get(0));
                    result.put("certified", 1);
                } else {
                    result.put("legalPersonIDCardNumber", "");
                    result.put("certified", 0);
                }
                String  businessLicense = (String) result.get("businessLicense");
                if (StrUtil.isNotBlank(businessLicense)) {
                    String imgSignURL = OssUtil.getImgSignURL(businessLicense, 0);
                    result.put("businessLicenseURL", imgSignURL);
                } else {
                    result.put("businessLicenseURL", "");
                }

                String address = (String) result.get("address");
                String areacode = (String) result.get("areacode");
                if (StrUtil.isNotBlank(address) && StrUtil.isBlank(areacode)) {
                    JSONObject geo = MapUtil.getGeoByAddress(address);

                    if (geo != null) {
                        String adcode = geo.getString("adcode");
                        if (StrUtil.isNotEmpty(adcode)) {
                            String fetchedAreaCode = adcode.substring(0, 6) + "000000";
                            sb.delete(0, sb.length());
                            sb.append("select xzqhmc from t_xzqh where xzqhdm = ?");
                            List<Map<String, Object>> list3 = this.getJdbcTemplate_newg().queryForList(sb.toString(),
                                    new Object[]{fetchedAreaCode});
                            if (list3.size() > 0) {
                                result.put("areacode", fetchedAreaCode);
                                result.put("areaname", list3.get(0).get("xzqhmc"));
                            }
                        }
                    }
                }
            }

            res.setRetCode(0);
            res.setRetMessage("OK");
            res.setPage(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public PageData getCertificatedIDCardImageByCompanyId(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询失败");
        String companyId = (String) req.getReqData().get("companyId");
        if (StrUtil.isBlank(companyId)) {
            return res;
        }
        try {
            StringBuilder sb = new StringBuilder();
            sb.append("select idcard_image_front idCard, idcard_images_back idCardBack from baidu_realface a")
                    .append(" left join t_company b on a.business_id = b.cretUserId")
                    .append(" where b.companyId = ? and a.status = 1")
                    .append(" order by a.modifyTime desc limit 1");
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString(),
                    new Object[]{companyId});
            if (list.size() > 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
                res.setPage(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }
    
    public PageData updateCompany(DataReq req) {
        PageData res = new PageData();
        HashMap<String, Object> param = req.getReqData();
        res.setRetCode(9);
        res.setRetMessage("修改失败");
        StringBuffer sb = new StringBuffer();
        newg_status = begin_newg();// 开启事物
        try {
            sb.delete(0, sb.length());
            sb.append("update t_company set nes_companyId='").append(param.get("nes_companyId"))
            .append("' where companyId='").append(param.get("companyId")).append("' ");
            int i = this.getJdbcTemplate_newg().update(sb.toString());
            if(i>0) {
            	res.setRetCode(0);
                res.setRetMessage("OK");
                commit_newg(newg_status);
            }else{
            	res.setRetCode(0);
                res.setRetMessage("OK");
				rollback_newg(newg_status);
				
			}
        } catch (Exception e) {
        	rollback_newg(newg_status);
            e.printStackTrace();
        } finally {
            rollback_newg(newg_status);
        }
        return res;
    }
    
    public PageData getCompanyAllList(DataReq req) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("查询失败");
        
        try {
            StringBuilder sb = new StringBuilder();
            sb.append("select * from t_company where sfsc=0 ");
            if(!"00".equals(req.getUinfo().getCompanyId())) {
            	sb.append(" and companyId like '").append(req.getUinfo().getCompanyId()).append("%' ");
            }
            List<Map<String, Object>> list = this.getJdbcTemplate_newg().queryForList(sb.toString());
            if (list.size() > 0) {
                res.setRetCode(0);
                res.setRetMessage("OK");
                res.setPage(list);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public List<Map<String, Object>> getCompanyByCompanyId(DataReq req) {
        Map<String, Object> param = req.getReqData();
        List<Map<String, Object>> list=new ArrayList<>();
        try {
            Object companyId = ObjectUtil.isEmpty(param.get("companyId")) ? req.getUinfo().getCompanyId() : param.get("companyId");
            StringBuilder sb = new StringBuilder();
            sb.append("select * from t_company where sfsc=0 ");
            if(!"00".equals(req.getUinfo().getCompanyId())) {
                sb.append(" and companyId = ?");
            }
            list = this.getJdbcTemplate_newg().queryForList(sb.toString(),new Object[]{companyId});
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public PageData deleteAllCompany(String companyId) {
        PageData res = new PageData();
        res.setRetCode(9);
        res.setRetMessage("删除失败");
        StringBuffer sb = new StringBuffer();
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("companyId",companyId);
        //String companyId = (String) param.get("companyId");
        if(StrUtil.isBlank(companyId)){
            res.setRetMessage("公司id不能为空");
            return res;
        }
        JdbcTemplate jdbcTemplate_newg = this.getJdbcTemplate_newg();
        sb.append("select companyId from t_company where FIND_IN_SET(p_companyId,getChild('").append(companyId).append("'))");
        List<Map<String, Object>> lists = jdbcTemplate_newg.queryForList(sb.toString());

        newg_status = begin_newg();
        try {
            for (Map<String, Object> map : lists) {
                String companyId1 = (String) map.get("companyId");
                //公司下级
                sb.delete(0, sb.length());
                sb.append("delete from t_company where companyId= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
            }
            lists.add(0,param);
            for (Map<String, Object> maps : lists) {
                String companyId1 = (String) maps.get("companyId");
                //部门
                sb.delete(0, sb.length());
                sb.append("SELECT departmentId FROM t_user where role_id = '419190a4-1a6f-42b9-b4f1-254224c15ad7' and companyId= '").append(companyId1).append("'");
                List<Map<String, Object>> departmentIdList = jdbcTemplate_newg.queryForList(sb.toString());
                sb.delete(0, sb.length());
                sb.append("delete from t_department where 1=1  ");
                if (departmentIdList !=null && departmentIdList.size()>0){
                    sb.append(" and departmentId != ' ").append(departmentIdList.get(0).get("departmentId")).append("'");
                }
                sb.append(" and departmentId like '").append(companyId1).append("%' ") ;
                jdbcTemplate_newg.update(sb.toString(),new Object[]{});
                //活动
                sb.delete(0, sb.length());
                sb.append("delete from active_custom where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from active_file where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from active_record  where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from active_session where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append(" delete active_user,b from active_user \n" +
                        " left join active_user_address b on active_user.uuid =b.activeUserId \n" +
                        " where active_user.companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                 //pc 活动
                sb.delete(0, sb.length());
                sb.append("delete from pc_active where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from pc_active_award_record where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from pc_active_lucky_user where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from pc_active_reward where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                 //落地页活动
                sb.delete(0, sb.length());
                sb.append(" delete t_activity,b from t_activity  \n" +
                        " left join t_activity_collect b on t_activity.uuid =b.activeId \n" +
                        " where t_activity.companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_expand where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_external_permission where company_id = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_scene where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_scene_param where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                //log_operate log_claim
                sb.delete(0, sb.length());
                sb.append("delete from log_operate where companyId= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from log_claim where companyId= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from d_demodata where companyId= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                //商机
                sb.delete(0, sb.length());
                sb.append("delete opportunity,opportunity_bim,opportunity_file,opportunity_quote,opportunity_staff \n" +
                        "from opportunity \n" +
                        "left join opportunity_bim on opportunity.uuid =opportunity_bim.opportunityId \n" +
                        "left join opportunity_file on opportunity.uuid=opportunity_file.opportunityId \n" +
                        "left join opportunity_quote on opportunity.uuid=opportunity_quote.opportunityId \n" +
                        "left join opportunity_staff on opportunity.uuid =opportunity_staff.opportunityId \n" +
                        "where opportunity.companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(), new Object[]{companyId1});
                //项目
                sb.delete(0, sb.length());
                sb.append("delete from t_project_bulid_log where company_id= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_project_reporting where company_id= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append(" delete t_project,b,c,d from t_project  \n" +
                        " left join t_project_check b on t_project.uuid =b.project_uuid  \n" +
                        " left join t_project_record c on t_project.uuid =c.project_uuid  \n" +
                        " left join t_project_scheduling d on t_project.uuid =d.project_uuid  \n" +
                        " where t_project.company_id  = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                //客户
                 //日志
                sb.delete(0, sb.length());
                sb.append("delete t_logs from t_logs  \n" +
                        "left join t_user tu on tu.id =t_logs.uid  \n" +
                        "where tu.companyId  =? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_sign where company_id= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_work_order_info where company_id= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_work_order_record where company_id= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_user_points where company_id= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_user_points_rule where company_id= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete t_project_log from t_project_log \n" +
                        "left join t_user tu on t_project_log.createUid =tu.id \n" +
                        "where tu.companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from t_user \n"+
                        " where role_id <>'419190a4-1a6f-42b9-b4f1-254224c15ad7' and companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete customer,cf,cs from customer \n" +
                        "left join customer_file cf on customer.uuid =cf.customerId \n" +
                        "left join customer_staff cs on customer.uuid =cs.customerId \n" +
                        "where customer.companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});

                //角色
                sb.delete(0, sb.length());
                sb.append("delete t_role,trm from t_role\n" +
                        "left join t_role_model trm on t_role.role_id =trm.role_id  \n" +
                        "where t_role.role_id <>'419190a4-1a6f-42b9-b4f1-254224c15ad7' and t_role.companyId  = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                //采购订单
                sb.delete(0, sb.length());
                sb.append("delete from g_purchase where companyId= ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                //线索
                sb.delete(0, sb.length());
                sb.append("delete from clue where cluebelongtocompany = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                //合同
                sb.delete(0, sb.length());
                sb.append("delete from contract_info where company_id = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from contract_serial_number where company_id = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from contract_type where company_id = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                //商品
                sb.delete(0, sb.length());
                sb.append("delete from g_goods where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from g_goodstype where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                //订单出库 进库
                sb.delete(0, sb.length());
                sb.append("delete from g_order_in where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from g_order_in_apply where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from g_order_in_tmp where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from g_order_out where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from g_order_out_apply where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from g_order_out_tmp where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from g_purchase where companyGoodsId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});
                sb.delete(0, sb.length());
                sb.append("delete from g_warehouse where companyId = ? ");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1});

                sb.delete(0, sb.length());
                sb.append("delete from t_agent_facturer where facturer_id = ? or agent_id=?");
                jdbcTemplate_newg.update(sb.toString(),new Object[]{companyId1,companyId1});
            }
            commit_newg(newg_status);
            res.setRetCode(0);
            res.setRetMessage("OK");
        } catch (Exception e) {
            e.printStackTrace();
            rollback_newg(newg_status);
        }
        return res;
    }
}
